import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;


public class ScoreDao {
	public static ArrayList<Score> queryScoreList() {
		 ArrayList<Score> scoreList = new ArrayList<>();
		/**
		 * 查询学生列表
		 */
			Connection conn = ConnectionUtil.getConnection();
	        Statement stmt = null;
	        try{
	           
	            // 执行查询
	            System.out.println(" 实例化Statement对象...");
	            stmt = conn.createStatement();
	            String sql;
	            
	            sql = "SELECT id, kch, cj, kkxq FROM score ";
	            ResultSet rs =  stmt.executeQuery(sql);
	        
	            // 展开结果集数据库
	            while(rs.next()){
	                // 通过字段检索
	                int id  = rs.getInt("id");
	                int kch = rs.getInt("kch");
	                int cj = rs.getInt("cj"); 
	                int kkxq=rs.getInt("kkxq");
	                
	                Score scoree = new Score(id, kch, cj, kkxq);
					scoreList.add(scoree);
	            }
	     /*//完成后关闭
           rs.close();
           stmt.close();*/
       }catch(SQLException se){
           // 处理 JDBC 错误
           se.printStackTrace();
       }catch(Exception e){
           // 处理 Class.forName 错误
           e.printStackTrace();
       }/*finally{
           // 关闭资源
           try{
               if(stmt!=null) stmt.close();
           }catch(SQLException se2){
           }// 什么都不做
           try{
               if(conn!=null) conn.close();
           }catch(SQLException se){
               se.printStackTrace();
           }
       }*/
	        System.out.println("查询成功！");
	        return scoreList;	
	}

	public static void addUsePrepareStatement(Score score) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into score values(?,?,?,?)";
		try {
			PreparedStatement psta = (PreparedStatement) conn.prepareStatement(sql);
			psta.setString(1, score.getName());
			psta.setInt(2, score.getId());
			psta.setInt(3, score.getKch());
			psta.setInt(4, score.getCj());

			
			int result = psta.executeUpdate();
			if(result > 0) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
     	}
		
	}

	public static void del(String name, String kcm, String nme) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from score where cj = '"+name+"'";
		
		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if(result > 0) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
